package rohyunji;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CompanyDAO extends _SubDAO

{
	String insertSQL = " insert into COMPANY (cp_no,mb_no,cp_reg_no,cp_name,"
			+ " cp_pre,cp_addr,cp_phone,job_no,cp_scale,cp_content,cp_incor_day)"
			+ " values(exam.nextval,?,?,?,?,?,?,?,?,?,?) ";
	
	
	
	String selectSQL = "select cp_reg_no,cp_name,cp_pre,cp_addr,cp_phone,job_no,cp_scale,cp_content,"
			+ "cp_incor_day from company where cp_no=?";

	String updateSQL = "update  COMPANY set cp_reg_no=?," +
			"cp_name=?,cp_pre=?,cp_addr=?,cp_phone=?,job_no=?,cp_scale=?,cp_content=? where cp_no=?";
	
	String deleteSQL="delete from company where cp_no=?";
	
	String selectCp_no="select  cp_no  from company where mb_no=?";
	

	@Override
	public boolean InsertCompany(CompanyVO cv) {

		// TODO Auto-generated method stub
		Connection c = null;
		PreparedStatement p = null;
		boolean result = false;
		c = openDataBase();
		try {
			p = c.prepareStatement(insertSQL);
			p.setString(1,cv.getMb_no());
			p.setString(2, cv.getCp_reg_no());
			p.setString(3, cv.getCp_name());
			p.setString(4, cv.getCp_pre());
			p.setString(5, cv.getCp_addr());
			p.setString(6, cv.getCp_phone());
			p.setString(7, cv.getCp_job_type());
			p.setString(8, cv.getCp_scale());
			p.setString(9, cv.getCp_content());
			p.setString(10, cv.getCp_incor_day());

			p.executeUpdate();
			result = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
	}

	

	@Override
	public boolean UpdateCompany(CompanyVO cv) {
		// TODO Auto-generated method stub
		Connection c = null;
		PreparedStatement p = null;
		boolean result = false;
		c = openDataBase();
		try {
			p = c.prepareStatement(updateSQL);

			p.setString(1, cv.getCp_reg_no());
			p.setString(2, cv.getCp_name());
			p.setString(3, cv.getCp_pre());
			p.setString(4, cv.getCp_addr());
			p.setString(5, cv.getCp_phone());
			p.setString(6, cv.getCp_job_type());
			p.setString(7, cv.getCp_scale());
			p.setString(8, cv.getCp_content());
			p.setString(9, cv.getCp_no());
			
		
			

			
			p.executeUpdate();
			result = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return result;
		
	
	}
	
	@Override
	public boolean deleteCompany(CompanyVO cv) {

		Connection c = null;
		PreparedStatement p = null;
		boolean result = false;
		c = openDataBase();
		try {
			p = c.prepareStatement(deleteSQL);


			p.setString(1, cv.getCp_no());
			
			p.executeUpdate();
			result = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	
	return result;
}
	
	
	@Override
	public List<CompanyVO> SelectCompany(CompanyVO cv) {

		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;
		List<CompanyVO> result = new ArrayList<>();
	

		try {
			c = openDataBase();
			p = c.prepareStatement(selectSQL);
			p.setString(1, cv.getCp_no());
			rs = p.executeQuery();
			while (rs.next()) {
				
				CompanyVO cvo = new CompanyVO(); 
					cvo.setCp_reg_no(rs.getString(1));
					cvo.setCp_name(rs.getString(2));
					cvo.setCp_pre(rs.getString(3));
					cvo.setCp_addr(rs.getString(4));
					cvo.setCp_phone(rs.getString(5));
					cvo.setCp_job_type(rs.getString(6));
					cvo.setCp_scale(rs.getString(7));
					cvo.setCp_content(rs.getString(8));
					cvo.setCp_incor_day(rs.getString(9));
				result.add(cvo);

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDataBase(c, p, rs);
		}

		return result;
	}
	
	
	
	
	
	
	@Override
	public List<CompanyVO> selectCp_no(CompanyVO cv) {
		
		Connection c = null;
		PreparedStatement p = null;
		ResultSet rs = null;
		
		List<CompanyVO> result = new ArrayList<>();
		
		
		
		
		try {
				c = openDataBase();
			
			
				p = c.prepareStatement(selectCp_no);
				p.setString(1, cv.getMb_no());
				
				rs = p.executeQuery();
			
		
			while (rs.next()) {
				
					CompanyVO cvo = new CompanyVO(); 
					cvo.setCp_no(rs.getString("cp_no"));
				
					result.add(cvo);

			}
			

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDataBase(c, p, rs);
		}

		return result;
	}

	




}
